﻿using Dapper;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using WebTest.Infrastructure.Interfaces;

namespace WebTest.Infrastructure.Dapper
{
    public class DataAccess : IDataAccess
    {
        private readonly IConfiguration configuration;
        private readonly ILogger<DataAccess> logger;

        public DataAccess(IConfiguration configuration)
        {
            this.configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
            logger = NullLogger<DataAccess>.Instance;
        }

        public IDbConnection DbConnection()
        {
            return new MySqlConnection(configuration.GetConnectionString("MySql"));
        }

        public bool CloseConnection(IDbConnection connection)
        {
            bool result = false;
            try
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
                result = true;
            }
            catch (Exception ex)
            {
                logger.LogError($"数据库连接关闭失败，错误信息：{ex.Message}");
                result = false;
            }
            return result;
        }

        public T Execute<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            T obj = default(T);
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                if (hasTransaction)
                {
                    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            if (commandType == CommandType.Text)
                            {
                                obj = connection.QueryFirstOrDefault<T>(sql, param, transaction, null, CommandType.Text);
                            }
                            else
                            {
                                obj = connection.QueryFirstOrDefault<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            logger.LogError($"SQL语句：{sql}，使用系统事务执行 Execute 方法出错，错误信息：{ex.Message}");
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (commandType == CommandType.Text)
                        {
                            obj = connection.QueryFirstOrDefault<T>(sql, param, null, null, CommandType.Text);
                        }
                        else
                        {
                            obj = connection.QueryFirstOrDefault<T>(sql, param, null, null, CommandType.StoredProcedure);
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.LogError($"SQL语句：{sql}，不使用系统事务执行 Execute 方法出错，错误信息：{ex.Message}");
                    }
                }
            }
            return obj;
        }

        public T Execute<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
        {
            T obj = default(T);
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            try
            {
                if (commandType == CommandType.Text)
                {
                    obj = connection.QueryFirstOrDefault<T>(sql, param, transaction, null, CommandType.Text);
                }
                else
                {
                    obj = connection.QueryFirstOrDefault<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"SQL语句：{sql}，使用外部事务执行 Execute 方法出错，错误信息：{ex.Message}");
                throw ex;
            }
            return obj;
        }

        public async Task<T> ExecuteAsync<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            T obj = default(T);
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                if (hasTransaction)
                {
                    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            if (commandType == CommandType.Text)
                            {
                                obj = await connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction, null, CommandType.Text);
                            }
                            else
                            {
                                obj = await connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            logger.LogError($"SQL语句：{sql}，使用系统事务执行 ExecuteAsync 方法出错，错误信息：{ex.Message}");
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (commandType == CommandType.Text)
                        {
                            obj = await connection.QueryFirstOrDefaultAsync<T>(sql, param, null, null, CommandType.Text);
                        }
                        else
                        {
                            obj = await connection.QueryFirstOrDefaultAsync<T>(sql, param, null, null, CommandType.StoredProcedure);
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.LogError($"SQL语句：{sql}，不使用系统事务执行 ExecuteAsync 方法出错，错误信息：{ex.Message}");
                    }
                }
            }
            return obj;
        }

        public async Task<T> ExecuteAsync<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
        {
            T obj = default(T);
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            try
            {
                if (commandType == CommandType.Text)
                {
                    obj = await connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction, null, CommandType.Text);
                }
                else
                {
                    obj = await connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"SQL语句：{sql}，使用外部事务执行 ExecuteAsync 方法出错，错误信息：{ex.Message}");
                throw ex;
            }
            return obj;
        }

        public IList<T> ExecuteIList<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            IList<T> list = null;
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                if (hasTransaction)
                {
                    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            if (commandType == CommandType.Text)
                            {
                                list = connection.Query<T>(sql, param, transaction, true, null, CommandType.Text).ToList();
                            }
                            else
                            {
                                list = connection.Query<T>(sql, param, transaction, true, null, CommandType.StoredProcedure).ToList();
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            logger.LogError($"SQL语句：{sql}，使用系统事务执行 ExecuteIList<T> 方法出错，错误信息：{ex.Message}");
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (commandType == CommandType.Text)
                        {
                            list = connection.Query<T>(sql, param, null, true, null, CommandType.Text).ToList();
                        }
                        else
                        {
                            list = connection.Query<T>(sql, param, null, true, null, CommandType.StoredProcedure).ToList();
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.LogError($"SQL语句：{sql}，不使用系统事务执行 ExecuteIList<T> 方法出错，错误信息：{ex.Message}");
                    }
                }
            }
            return list;
        }

        public IList<T> ExecuteIList<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
        {
            IList<T> list = null;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            try
            {
                if (commandType == CommandType.Text)
                {
                    list = connection.Query<T>(sql, param, transaction, true, null, CommandType.Text).ToList();
                }
                else
                {
                    list = connection.Query<T>(sql, param, transaction, true, null, CommandType.StoredProcedure).ToList();
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"SQL语句：{sql}，使用外部事务执行 ExecuteIList<T> 方法出错，错误信息：{ex.Message}");
                throw ex;
            }
            return list;
        }

        public async Task<IList<T>> ExecuteIListAsync<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            IList<T> list = null;
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                if (hasTransaction)
                {
                    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            if (commandType == CommandType.Text)
                            {
                                var data = await connection.QueryAsync<T>(sql, param, transaction, null, CommandType.Text);
                                list = data.ToList();
                            }
                            else
                            {
                                var data = await connection.QueryAsync<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                                list = data.ToList();
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            logger.LogError($"SQL语句：{sql}，使用系统事务执行 ExecuteIListAsync<T> 方法出错，错误信息：{ex.Message}");
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (commandType == CommandType.Text)
                        {
                            var data = await connection.QueryAsync<T>(sql, param, null, null, CommandType.Text);
                            list = data.ToList();
                        }
                        else
                        {
                            var data = await connection.QueryAsync<T>(sql, param, null, null, CommandType.StoredProcedure);
                            list = data.ToList();
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.LogError($"SQL语句：{sql}，不使用系统事务执行 ExecuteIListAsync<T> 方法出错，错误信息：{ex.Message}");
                    }
                }
            }
            return list;
        }

        public async Task<IList<T>> ExecuteIListAsync<T>(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
        {
            IList<T> list = null;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            try
            {
                if (commandType == CommandType.Text)
                {
                    var data = await connection.QueryAsync<T>(sql, param, transaction, null, CommandType.Text);
                    list = data.ToList();
                }
                else
                {
                    var data = await connection.QueryAsync<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                    list = data.ToList();
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"SQL语句：{sql}，使用外部事务执行 ExecuteIListAsync<T> 方法出错，错误信息：{ex.Message}");
            }
            return list;
        }

        public int ExecuteNonQuery(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            int result = 0;
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                if (hasTransaction)
                {
                    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            if (commandType == CommandType.Text)
                            {
                                result = connection.Execute(sql, param, transaction, null, CommandType.Text);
                            }
                            else
                            {
                                result = connection.Execute(sql, param, transaction, null, CommandType.StoredProcedure);
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            logger.LogError($"SQL语句：{sql}，使用系统事务执行 ExecuteNonQuery 方法出错，错误信息：{ex.Message}");
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (commandType == CommandType.Text)
                        {
                            result = connection.Execute(sql, param, null, null, CommandType.Text);
                        }
                        else
                        {
                            result = connection.Execute(sql, param, null, null, CommandType.StoredProcedure);
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.LogError($"SQL语句：{sql}，不使用系统事务执行 ExecuteNonQuery 方法出错，错误信息：{ex.Message}");
                    }
                }
            }
            return result;
        }

        public int ExecuteNonQuery(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
        {
            int result = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            try
            {
                if (commandType == CommandType.Text)
                {
                    result = connection.Execute(sql, param, transaction, null, CommandType.Text);
                }
                else
                {
                    result = connection.Execute(sql, param, transaction, null, CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"SQL语句：{sql}，使用外部事务执行 ExecuteNonQuery 方法出错，错误信息：{ex.Message}");
            }
            return result;
        }

        public async Task<int> ExecuteNonQueryAsync(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            int result = 0;
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                if (hasTransaction)
                {
                    using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        try
                        {
                            if (commandType == CommandType.Text)
                            {
                                result = await connection.ExecuteAsync(sql, param, transaction, null, CommandType.Text);
                            }
                            else
                            {
                                result = await connection.ExecuteAsync(sql, param, transaction, null, CommandType.StoredProcedure);
                            }
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            logger.LogError($"SQL语句：{sql}，使用系统事务执行 ExecuteNonQueryAsync 方法出错，错误信息：{ex.Message}");
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (commandType == CommandType.Text)
                        {
                            result = await connection.ExecuteAsync(sql, param, null, null, CommandType.Text);
                        }
                        else
                        {
                            result = await connection.ExecuteAsync(sql, param, null, null, CommandType.StoredProcedure);
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.LogError($"SQL语句：{sql}，不使用系统事务执行 ExecuteNonQueryAsync 方法出错，错误信息：{ex.Message}");
                    }
                }
            }
            return result;
        }

        public async Task<int> ExecuteNonQueryAsync(string sql, object param, IDbTransaction transaction, IDbConnection connection, CommandType commandType = CommandType.Text)
        {
            int result = 0;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            try
            {
                if (commandType == CommandType.Text)
                {
                    result = await connection.ExecuteAsync(sql, param, transaction, null, CommandType.Text);
                }
                else
                {
                    result = await connection.ExecuteAsync(sql, param, transaction, null, CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                logger.LogError($"SQL语句：{sql}，使用外部事务执行 ExecuteNonQueryAsync 方法出错，错误信息：{ex.Message}");
            }
            return result;
        }

        public T ExecuteScalar<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            T result;
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                try
                {
                    if (hasTransaction)
                    {
                        using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                        {
                            if (commandType == CommandType.Text)
                            {
                                result = connection.ExecuteScalar<T>(sql, param, transaction, null, CommandType.Text);
                            }
                            else
                            {
                                result = connection.ExecuteScalar<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                            }
                        }
                    }
                    else
                    {
                        if (commandType == CommandType.Text)
                        {
                            result = connection.ExecuteScalar<T>(sql, param, null, null, CommandType.Text);
                        }
                        else
                        {
                            result = connection.ExecuteScalar<T>(sql, param, null, null, CommandType.StoredProcedure);
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.LogError($"SQL语句：{sql}，执行 ExecuteScalar<T> 方法出错，错误信息：{ex.Message}");
                    result = default;
                }
            }
            return result;
        }

        public async Task<T> ExecuteScalarAsync<T>(string sql, object param, bool hasTransaction = false, CommandType commandType = CommandType.Text)
        {
            T result;
            using (var connection = DbConnection())
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                try
                {
                    if (hasTransaction)
                    {
                        using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                        {
                            if (commandType == CommandType.Text)
                            {
                                result = await connection.ExecuteScalarAsync<T>(sql, param, transaction, null, CommandType.Text);
                            }
                            else
                            {
                                result = await connection.ExecuteScalarAsync<T>(sql, param, transaction, null, CommandType.StoredProcedure);
                            }
                        }
                    }
                    else
                    {
                        if (commandType == CommandType.Text)
                        {
                            result = await connection.ExecuteScalarAsync<T>(sql, param, null, null, CommandType.Text);
                        }
                        else
                        {
                            result = await connection.ExecuteScalarAsync<T>(sql, param, null, null, CommandType.StoredProcedure);
                        }
                    }
                }
                catch (Exception ex)
                {
                    logger.LogError($"SQL语句：{sql}，执行 ExecuteScalarAsync<T> 方法出错，错误信息：{ex.Message}");
                    result = default;
                }
            }
            return result;
        }
    }
}
